package com.ny.dao.impl;

import com.ny.dao.ProductDao;
import com.ny.entity.Product;
import com.ny.until.DbUntil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class ProductDaoImpl implements ProductDao {
    DbUntil dbUntil = new DbUntil();

    @Override
    public List<Product> findAllProduct() throws SQLException, ClassNotFoundException {
        // 1.连接数据库
        Connection connection = dbUntil.getCon();
        // 2.编写sql语句
        String sql = "select * from products";
        // 3.执行sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 4.得到sql语句的结果
        ResultSet resultSet = preparedStatement.executeQuery();
        // 5.创建一个list集合，存储Product对象
        List<Product> productList = new ArrayList<Product>();
        while (resultSet.next()) {
            // 把获取到的列名值交给Product对象
            Product product = new Product();
            int id = resultSet.getInt("product_id");
            product.setProductId(id);
            product.setProductName(resultSet.getString("name"));
            product.setPrice(resultSet.getDouble("price"));
            product.setStock(resultSet.getInt("stock"));
            product.setCategory(resultSet.getString("category"));
            product.setDescription(resultSet.getString("description"));
            // 把获取到的Product对象交给List集合
            productList.add(product);
        }
        return productList;
    }

    @Override
    public int updateProductStock(int productId, int number) throws SQLException, ClassNotFoundException {
        Connection  connection = dbUntil.getCon();
        String sql = "update products set stock=stock-? where product_id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, number);
        preparedStatement.setInt(2, productId);
        return preparedStatement.executeUpdate();
    }

    @Override
    public int insertProduct(Product product) throws Exception {
        Connection connection = dbUntil.getCon();
        String sql = "insert into products(name,price,stock,category,description) values(?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, product.getProductName());
        preparedStatement.setDouble(2, product.getPrice());
        preparedStatement.setInt(3, product.getStock());
        preparedStatement.setString(4, product.getCategory());
        preparedStatement.setString(5, product.getDescription());
        return preparedStatement.executeUpdate();

    }

    @Override
    public Product getProductById(int id) throws Exception {
        Connection connection = dbUntil.getCon();
        String sql = "select * from products where product_id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet resultSet =  preparedStatement.executeQuery();
        if (resultSet.next()) {
            Product product = new Product();
            product.setProductId(resultSet.getInt("product_id"));
            product.setProductName(resultSet.getString("name"));
            product.setPrice(resultSet.getDouble("price"));
            product.setStock(resultSet.getInt("stock"));
            product.setCategory(resultSet.getString("category"));
            product.setDescription(resultSet.getString("description"));
            return product;
        }
        return null;
    }

    @Override
    public int deleteProduct(int id) throws Exception {
        return dbUntil.getCon().prepareStatement("delete from products where product_id=?").executeUpdate();
    }

    @Override
    public int updateProduct(Product product) throws Exception {
        return dbUntil.getCon().prepareStatement("update products set name=?,price=?,stock=?,category=?,description=? where product_id=?").executeUpdate();
    }
}